package com.config.dao;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.simple.ParameterizedBeanPropertyRowMapper;
import org.springframework.stereotype.Repository;

import com.base.dao.TemplateDaoImpl;
import com.config.entity.ChargeConfig;


@Repository(value="chargeConfigDao")
public class ChargeConfigDaoImpl extends TemplateDaoImpl<ChargeConfig,Integer> implements ChargeConfigDao{
	/**
	 * 根据时间查询正在使用的电费计价模式(父节点记录)
	 * @param belongTime
	 * @return
	 */
	@Override
	public ChargeConfig findChargeType() {
		StringBuffer sql=new StringBuffer("select * from chargeConfig where   isEffective='Y'");
		Map<String, Object> params = new HashMap<String, Object>();
		return jt.queryForObject(sql.toString(), params,ParameterizedBeanPropertyRowMapper.newInstance(ChargeConfig.class));	
	}
	/**
	 * 查询半小时区间电费配置方式
	 * @param currentTime 
	 * @param beforeTime   
	 * @return
	 */
	@Override
	public ChargeConfig findByTime(Date belongTime) {
		String time="";String belong="";
		try {
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		SimpleDateFormat sdt = new SimpleDateFormat("HH:mm:ss");
		time=sdt.format(belongTime);
		belong="2016-01-01"+" "+time;
		ChargeConfig config=this.findChargeType();
		if(config!=null){
			List<ChargeConfig>list=this.getCharges(config.getId());
			if(list.size()!=0){
				Date biggerStartDate=list.get(0).getStartTime();
				for(int i=0;i<list.size();i++){
					if((i+1)<list.size()){
						if(list.get(i+1).getStartTime().before(biggerStartDate)){
							biggerStartDate=list.get(i+1).getStartTime();
						}
					}
				}
				if((sdf.parse(belong)).compareTo(biggerStartDate)<=0){
					belong="2016-01-02"+" "+time;
				}
			}
		}
		} catch (ParseException e) {
			e.printStackTrace();
		}
	    StringBuffer sql=new StringBuffer("select * from chargeConfig where  fatherId=(select id from chargeConfig where isEffective='Y') and CONVERT(varchar(19) , startTime, 120) <:belong  and CONVERT(varchar(19) , endTime, 120) >=:belong ");
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("belong", belong);
		return jt.queryForObject(sql.toString(), params,ParameterizedBeanPropertyRowMapper.newInstance(ChargeConfig.class));	
	}
	 /**
	  * 根据父节点id查询该节点所有电费配置
	  * @param fatherId
	  * @return
	  */
	@Override
	public List<ChargeConfig> getCharges(int fatherId) {
		StringBuffer sql=new StringBuffer("select * from chargeConfig where  fatherId=:fatherId order by startTime asc");
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("fatherId", fatherId);
		return jt.query(sql.toString(),params,ParameterizedBeanPropertyRowMapper.newInstance(ChargeConfig.class));
	}
	 /**
	  * 查询所有不同的chargeName名称
	  * @return
	  */
	@Override
	public List<ChargeConfig> findDistinctChargesName() {
		StringBuffer sql=new StringBuffer("select distinct(chargeName) from chargeConfig where  fatherId=(select id  from chargeConfig where   isEffective='Y')");
		Map<String, Object> params = new HashMap<String, Object>();
		return jt.query(sql.toString(),params,ParameterizedBeanPropertyRowMapper.newInstance(ChargeConfig.class));
	}
	
	/**
	 * treegrid显示所有电费配置
	 */
	@Override
	public List<ChargeConfig> findByTreePage(Integer id, int start, int limit) {
		StringBuilder sb = new StringBuilder("select top "+limit+" * from (")
		.append("select row_number() over(order by id asc ) rowindex,* from ")
		.append("chargeConfig where 1=1");
	Map<String, Object> params=new HashMap<String, Object>();
	if(id==null||id.equals(null)){
		sb.append(" and fatherId is NULL");
	}else{
		sb.append(" and fatherId=:id");
		params.put("id", id);
	}
	sb.append(") u where u.rowindex>:start");
	params.put("start", (start-1)*limit);
	params.put("limit", limit);
	return jt.query(sb.toString(),params,
			ParameterizedBeanPropertyRowMapper.newInstance(ChargeConfig.class));
	}
	
	@Override
	public int countByTreePage(Integer id) {
		StringBuffer sb = new StringBuffer("select count(*) from chargeConfig where 1=1");
		Map<String, Object> params=new HashMap<String, Object>();
		if( id==null||id.equals(null)){
			sb.append(" and fatherId is null");
		}else{
			sb.append(" and fatherId=:id");
			params.put("id", id);
		}
		return jt.queryForObject(sb.toString(), params, Integer.class);
	}
}
